package Database;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import DataStorageClasses.*;
import ExceptionClasses.DeletionFailedException;
import ExceptionClasses.InsertionFailureException;
import ExceptionClasses.InvalidDateBoundException;
import ExceptionClasses.InvalidReviewerAuthenticationException;

/*
 * IMPORTANT: After you are finished using an object of this time you must call disconnect()!!!
 * IMPORTANT: Business name should be gotten for the session variable for any methods that require you to be signed in to use
 */
public class DatabaseConnector 
{
	private String dbUrl = "jdbc:mysql://localhost/mydb";
	private String dbClass = "com.mysql.jdbc.Driver";
	private Connection con;
	
	public DatabaseConnector()
	{
		connect();
	}
	
	public void connect()
	{
		try {
			Class.forName(dbClass);
			con = DriverManager.getConnection (dbUrl, "root", "Brian");
			con.setAutoCommit(false);
		}

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			createDatabase();
			try {
				Class.forName(dbClass);
				con = DriverManager.getConnection (dbUrl, "root", "Brian");
				con.setAutoCommit(false);
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	private String readFileAsString(String filePath) throws java.io.IOException{
	    byte[] buffer = new byte[(int) new File(filePath).length()];
	    BufferedInputStream f = new BufferedInputStream(new FileInputStream(filePath));
	    f.read(buffer);
	    return new String(buffer);
	}
	
	public void createDatabase()
	{
		String path = System.getProperty("user.dir");
	    String fileName = path + "\\Database.txt";
	    
	    Statement pstmt = null;
	    Connection conn;
		try {
			Class.forName(dbClass);
			conn = DriverManager.getConnection ("jdbc:mysql://localhost", "root", "Brian");
			conn.setAutoCommit(false);
			try {
				String file = readFileAsString(fileName);
		      
				String[] queries = file.split(";");
				for (String query: queries) {
			      	pstmt = conn.createStatement();
			      	pstmt.execute(query);
				}
				conn.commit();
		    } catch (Exception e) {
		    	System.err.println("Error: " + e.getMessage());
		    	conn.rollback();
		    } finally {
		    	try {
		    		pstmt.close();
		    		conn.close();
		    	} catch (SQLException e) {
		    	  // TODO Auto-generated catch block
		    	  e.printStackTrace();
		    	} 
		    }
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void disconnect()
	{
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<String> getAllBusNames()
	{
		String query = "Select `idBusiness` FROM business ORDER BY `idBusiness`";
		Statement stmt;
		ArrayList<String> busList = new ArrayList<String>();
		
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			
			while(rs.next()) {
				busList.add(rs.getString("idBusiness"));
			}
			stmt.close();
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return busList;
	}
	
	public Business getProfile(String busName)
	{
		String query = "Select * FROM business WHERE '" + busName + "' LIKE idBusiness";
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) {
				String tel = rs.getString("Business Number");
				String fax = rs.getString("Fax Number");
				String email = rs.getString("Email");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				String post = rs.getString("Postal Code");
				String cell = rs.getString("cell number");
				
				Business bus = new Business(busName, tel, fax, cell, email, street, city, prov, post);
				stmt.close();
				return bus;
			}
			else {
				stmt.close();
				return null;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/*	if null returned then login failed because of incorrect business name login info
	 * 	throws: SQL Exception if there was an error with querying the database. Say something
	 * 	like "error accessing database. try again later."
	 */
	public Business signIn(String busName, String pass) throws SQLException
	{
		String query = "Select * FROM business WHERE '" + busName + "' = `idBusiness` AND '" + pass + "' = `password`";
		Statement stmt;

		stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		if(rs.next()) {
			String tel = rs.getString("Business Number");
			String fax = rs.getString("Fax Number");
			String email = rs.getString("Email");
			String street = rs.getString("Street Address");
			String city = rs.getString("City");
			String prov = rs.getString("Province");
			String post = rs.getString("Postal Code");
			String cell = rs.getString("cell number");
				
			Business bus = new Business(busName, tel, fax, cell, email, street, city, prov, post);
			stmt.close();	
			return bus;
		}
			
		else {
			stmt.close();
			return null;
		}
	}
	
	public Business addBusiness(String busName, String password, String busNum, String fax, String cell, String email, String streetAd, 
					String city, String province, String postalCode, String emailPass)
	{
		String query = "INSERT INTO mydb.business (`idBusiness`, `Password`, `Business Number`, `Fax Number`, `Email`, `Street Address`, `City`, `Province`, `Postal Code`, `cell number`, `email password`) " +
				"VALUES (?,?,?,?,?,?,?,?,?,?,?);"; 
					
		PreparedStatement statement;
		try {
			statement = con.prepareStatement(query);
			
			statement.setString(1, busName);
			statement.setString(2, password);
			statement.setString(3, busNum);
			statement.setString(4, fax);
			statement.setString(5, email);
			statement.setString(6, streetAd);
			statement.setString(7, city);
			statement.setString(8, province);
			statement.setString(9, postalCode);
			statement.setString(10, cell);
			statement.setString(11, emailPass);
			
			statement.execute();
			con.commit();
			statement.close();
			return signIn(busName, password);
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			
			return null;
		}
	}
	
	//SIGNED IN BUSINESS METHODS
	/*
	 * IMPORTANT!!! Must be signed in to use
	 */
	public boolean editBusiness(String busName, String password, String busNum, String fax, String cell, String email, String streetAd, 
					String city, String province, String postalCode)
	{
		String query = "UPDATE `mydb`.`Business` SET";
		boolean isfirst = true;
		boolean empty = true;
		
		if (password != null) {
			isfirst = false;
			empty = false;
			
			query += " `Password` = '" + password + "'";
		}
		if (busNum != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Business Number` = '" + busNum + "'";
			empty = false;
		}
		
		if (fax != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Fax Number` = '" + fax + "'";
			empty = false;
		}
		
		if (cell != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `cell number` = '" + cell + "'";
			empty = false;
		}
		
		if (email != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Email` = '" + email + "'";
			empty = false;
		}
		
		if (streetAd != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Street Address` = '" + streetAd + "'";
			empty = false;
		}
		
		if (city != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `City` = '" + city + "'";
			empty = false;
		}
		
		if (province != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Province` = '" + province + "'";
			empty = false;
		}
		
		if (postalCode != null)  {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Postal Code` = '" + postalCode + "'";
			empty = false;
		}
		
		if (empty) {
			return false;
		}
		
		query += " WHERE `idBusiness` = '" + busName + "';";
		
		try {
			Statement statement = con.createStatement();
			statement.executeUpdate(query);
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
				
		return false;
	}
	
	public boolean removeBusiness(String busName, String password)
	{
		String update = "DELETE FROM `mydb`.`Business` WHERE `idBusiness` = '" + busName + "' AND `Password` = '" + password + "';";
		
		try {
			Statement statement = con.createStatement();
			int deleted = statement.executeUpdate(update);
			
			if (deleted == 0) {
				statement.close();
				return false;
			}
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	public double getAmountOwing(Long clientid, String business, Date low, Date high)
	{
		String query = "SELECT SUM(`Actual Price` - `Amount Paid`) AS `amt owed` FROM `mydb`.`Job` WHERE `Business name` = '" + business + "'";
		
		if (clientid != null) {
			query += " AND `Clientid` = '" + clientid + "'";
		}
		
		if (low != null) {
			query += " AND `Actual Finish Date` >= '" + low + "'";
		}
		
		if (high != null) {
			query += " AND `Actual Finish Date` <= '" + high + "'";
		}
		
		query += ";";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if(rs.next()) {
				double ret = rs.getDouble("amt owed");
				statement.close();
				return ret;
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public double getTotalMoneyMade(String business, Date low, Date high)
	{
		String query = "SELECT SUM(`Amount Paid`) AS `total` FROM `mydb`.`Job` WHERE `Business name` = '" + business + "'";
					
		if (low != null) {
			query += " AND `Actual Finish Date` >= '" + low + "'";
		}
		
		if (high != null) {
			query += " AND `Actual Finish Date` <= '" + high + "'";
		}
		
		query += ";";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				double ret = rs.getDouble("total");
				statement.close();
				return ret;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public double getTotalWages(String business, Date low, Date high) 
	{
		String query = "SELECT `hours work`, `wage` FROM `mydb`.`TimeSheet`, `mydb`.`Employee` WHERE `Employee` = `idEmployee`" +
				" AND `Businessid` = '" + business + "'";
		if (low != null) {
			query += " AND `Date` >= '" + low + "'";
		}
		
		if (high != null) {
			query += " AND `Date` <= '" + high + "'";
		}
		
		query += ";";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			double total=0;
			
			while(rs.next()) {
				double hours = rs.getDouble("hours work");
				double wage = rs.getDouble("wage");
				
				if (hours > 8) {
					total += wage*(8 + (hours-8)*1.5);
				}
				else {
					total += wage*hours;
				}
			}
			statement.close();
			return total;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	public ArrayList<Client> getClientsOwingMoney()
	{
		return null;
	}
	
	
	public EmailInfo getEmailInfo(String business)
	{
		String query = "SELECT `email`, `email password` FROM `mydb`.`Business` WHERE `idBusiness` = '" + business + "'";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				String e = rs.getString("email");
				String p = rs.getString("email password");
				statement.close();
				return new EmailInfo( e, p );
			}
			
			else {
				statement.close();
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	//CLIENT METHODS
	/*
	 * IMPORTANT!!! Must be signed in to use
	 */
	public Client addClient(String first, String last, String email, String street, String city, String prov,
			String post, String tel1, String tel2, String cell, String busName)
	{
		String query1 = "INSERT INTO mydb.Client (`firstName`, `lastName`, `clientNum`, `email`, `Street Address`, `City`, `Province`, `Postal Code`, `telephoneNum1`, `telephoneNum2`, `cell number`, `Business`)" +
					"VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
		
		PreparedStatement statement1;
		
		try {
			statement1 = con.prepareStatement(query1, Statement.RETURN_GENERATED_KEYS);
			
			statement1.setString(1, first);
			statement1.setString(2, last);
			statement1.setLong(3, 0);
			statement1.setString(4, email);
			statement1.setString(5, street);
			statement1.setString(6, city);
			statement1.setString(7, prov);
			statement1.setString(8, post);
			statement1.setString(9, tel1);
			statement1.setString(10, tel2);
			statement1.setString(11, cell);
			statement1.setString(12, busName);
			
			statement1.execute();
			
			ResultSet rs = statement1.getGeneratedKeys();
			
			if (rs.next()) {
				int clientID = rs.getInt(1);
				
				con.commit();
				statement1.close();
				return new Client(clientID, first, last, email, street, city, prov, post, tel1, tel2, cell, busName);
			}
			statement1.close();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		
		return null;
	}
	
	/*
	 * If a field is not specified set it to null, id must not be null, cannot change clientID
	 */
	public boolean updateClient(long clientID, String first, String last, String email, String street, String city, String prov,
			String post, String tel1, String tel2, String cell)
	{
		String query = "UPDATE `mydb`.`client` SET";
		boolean prev = false;
		boolean empty = true;
		
		if (first != null){
			if (prev) {
				query += ",";
			}
			
			query += " `firstName` = '" + first + "'";
			
			prev = true;
			empty = false;
		}
		
		if (last != null){
			if (prev) {
				query += ",";
			}
			
			query += " `lastName` = '" + last + "'";
			
			prev = true;
			empty = false;
		}
		
		if (email != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Email` = '" + email + "'";
			
			prev = true;
			empty = false;
		}
		
		if (street != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Street Address` = '" + street + "'";
			
			prev = true;
			empty = false;
		}
		
		if (city != null){
			if (prev) {
				query += ",";
			}
			
			query += " `City` = '" + city + "'";
			
			prev = true;
			empty = false;
		}
		
		if (prov != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Province` = '" + prov + "'";
			
			prev = true;
			empty = false;
		}
		
		if (post != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Postal Code` = '" + post + "'";
			
			prev = true;
			empty = false;
		}
		
		if (tel1 != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `telephoneNum1` = '" + tel1 + "'";
			
			prev = true;
			empty = false;
		}
		
		if (tel2 != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `telephoneNum2` = '" + tel2 + "'";
			
			prev = true;
			empty = false;
		}
		
		if (cell != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `cell number` = '" + cell + "'";
			
			prev = true;
			empty = false;
		}
		
		if (empty) {
			return false;
		}
		
		query += " WHERE `clientNum` = '" + String.valueOf(clientID) + "';";
		
		try {
			Statement statement = con.createStatement();
			int updated = statement.executeUpdate(query);
			
			if (updated != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean removeClient(long clientID)
	{
		String query = "DELETE FROM `mydb`.`client` WHERE `clientNum` = '" + String.valueOf(clientID) + "';";
		
		try {
			Statement statement = con.createStatement();
			int removed = statement.executeUpdate(query);
			
			if (removed != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	public Client getClient(long clientID, String business)
	{
		String query = "SELECT * FROM `mydb`.`client` WHERE `clientNum` = '" + String.valueOf(clientID) + "'" +
				" AND `business` = '" + business + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				String first = rs.getString("firstName");
				String last = rs.getString("lastName");
				String email = rs.getString("email");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String province = rs.getString("Province");
				String postal = rs.getString("Postal Code");
				String tel1 = rs.getString("telephoneNum1");
				String tel2 = rs.getString("telephoneNum2");
				String cell = rs.getString("cell number");
				
				statement.close();
				return new Client(clientID, first, last, email, street, city, province, postal, tel1, tel2, cell, business);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<Client> getClients(String first, String last, String email, String street, String city, String prov,
			String post, String tel1, String tel2, String cell, String business)
	{
		boolean isFirst = true;
		String query = "SELECT * FROM `mydb`.`client`";
		
		if (first != null) {
			query += " WHERE";
			isFirst = false;
			
			query += " `firstName` = '" + first + "'";
		}
		
		if (last != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `lastName` = '" + last + "'";
		}
		
		if (email != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `email` = '" + email + "'";
		}
		
		if (street != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Street Address` = '" + street + "'";
		}
		
		if (city != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `City` = '" + city + "'";
		}
		
		if (prov != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Province` = '" + prov + "'";
		}
		
		if (post != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Postal Code` = '" + post + "'";
		}
		
		if (tel1 != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `telephoneNum1` = '" + tel1 + "'";
		}
		
		if (tel2 != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `telephoneNum2` = '" + tel2 + "'";
		}
		
		if (cell != null) {
			if (isFirst) {
				query += " WHERE";
				isFirst = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `cell number` = '" + cell + "'";
		}
		
		if (!isFirst) {
			query += " AND";
		}
		
		else {
			query += " WHERE";
		}
		
		query += " `clientNum` IN (SELECT `clientNum` FROM `mydb`.`client` WHERE `Business` = '" + business + "');";
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<Client> clientList = new ArrayList<Client>();
			long clientID;
			String first1, last1, email1, street1, city1, province1, postal1, tel11, tel21, cell1;
			
			while(rs.next()) {
				
				clientID = rs.getLong("clientNum");
				first1 = rs.getString("firstName");
				last1 = rs.getString("lastName");
				email1 = rs.getString("email");
				street1 = rs.getString("Street Address");
				city1 = rs.getString("City");
				province1 = rs.getString("Province");
				postal1 = rs.getString("Postal Code");
				tel11 = rs.getString("telephoneNum1");
				tel21 = rs.getString("telephoneNum2");
				cell1 = rs.getString("cell number");
				clientList.add(new Client(clientID, first1, last1, email1, street1, city1, province1, postal1, tel11, tel21, cell1, business));
			}
			statement.close();
			return clientList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	//EMPLOYEE MEHTODS
	/*
	 * IMPORTANT!!! Must be signed in to use
	 */
	
	public Employee addEmployee(String bus, String firstName, String lastName, Date start, Date finish, double wage, long SIN, String street, String city, String prov,
			String postal, String home, String cell)
	{
		//idEmployee, first name, last name, Businessid, Date Hired, Date Fired, Wage, SIN, Street Address, City, Province, Postal Code, Home Phone, Cell Phone
		String query = "INSERT INTO `mydb`.`Employee` (`first name`, `last name`, `Businessid`, `Date Hired`, `Date Fired`, `Wage`, " +
				"`SIN`, `Street Address`, `City`, `Province`, `Postal Code`, `Home Phone`, `Cell Phone`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);";
		
		try {
			PreparedStatement statement = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			
			statement.setString(1, firstName);
			statement.setString(2, lastName);
			statement.setString(3, bus);
			statement.setDate(4, start);
			statement.setDate(5, finish);
			statement.setDouble(6, wage);
			statement.setLong(7, SIN);
			statement.setString(8, street);
			statement.setString(9, city);
			statement.setString(10, prov);
			statement.setString(11, postal);
			statement.setString(12, home);
			statement.setString(13, cell);
			
			statement.execute();
			
			ResultSet rs = statement.getGeneratedKeys();
			
			if (rs.next()) {
				long empID = rs.getLong(1);
				
				con.commit();
				statement.close();
				return new Employee(empID, firstName, lastName, bus, start, finish, wage, SIN, street, city, prov, postal, home, cell);
			}
			
			con.rollback();
			statement.close();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean updateEmployee(long empID, String firstName, String lastName, Date start, Date finish, double wage, long SIN, String street, String city, String prov,
			String postal, String home, String cell)
	{
		String query = "UPDATE `mydb`.`employee` SET";
		boolean prev = false;
		boolean empty = true;
		
		if (firstName != null){
			if (prev) {
				query += ",";
			}
			
			query += " `first name` = '" + firstName + "'";
			
			prev = true;
			empty = false;
		}
		
		if (lastName != null){
			if (prev) {
				query += ",";
			}
			
			query += " `last name` = '" + lastName + "'";
			
			prev = true;
			empty = false;
		}
		
		if (start != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Date Hired` = '" + start + "'";
			
			prev = true;
			empty = false;
		}
		
		if (finish != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Date Fired` = '" + finish + "'";
			
			prev = true;
			empty = false;
		}
		
		if (wage > -1){
			if (prev) {
				query += ",";
			}
			
			query += " `wage` = '" + wage + "'";
			
			prev = true;
			empty = false;
		}
		
		if (SIN > 0){
			if (prev) {
				query += ",";
			}
			
			query += " `SIN` = '" + SIN + "'";
			
			prev = true;
			empty = false;
		}
		
		if (street != null){
			if (prev) {
				query += ",";
			}
			
			query += " `Street Address` = '" + street + "'";
			
			prev = true;
			empty = false;
		}
		
		if (city != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `City` = '" + city + "'";
			
			prev = true;
			empty = false;
		}
		
		if (prov != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `Province` = '" + prov + "'";
			
			prev = true;
			empty = false;
		}
		
		if (postal != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `Postal Code` = '" + postal + "'";
			
			prev = true;
			empty = false;
		}
		
		if (home != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `Home Phone` = '" + home + "'";
			
			prev = true;
			empty = false;
		}
		
		if (cell != null) {
			if (prev) {
				query += ",";
			}
			
			query += " `Cell Phone` = '" + cell + "'";
			
			prev = true;
			empty = false;
		}
		
		if (empty) {
			return false;
		}
		
		query += " WHERE `idEmployee` = '" + String.valueOf(empID) + "';";
		
		try {
			Statement statement = con.createStatement();
			
			int updated = statement.executeUpdate(query);
			
			if (updated != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		return false;
	}
	
	public Employee getEmployee(long empID, String business)
	{
		String query = "SELECT * FROM `mydb`.`employee` WHERE `idEmployee` = '" + String.valueOf(empID) + "'" +
				" AND `Businessid` = '" + business + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				String first = rs.getString("first name");
				String last = rs.getString("last name");
				Date start = rs.getDate("Date Hired");
				Date finished = rs.getDate("Date Fired");
				double wage = rs.getDouble("Wage");
				long SIN = rs.getLong("SIN");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				String post = rs.getString("Postal Code");
				String home = rs.getString("Home Phone");
				String cell = rs.getString("Cell Phone");
				
				statement.close();
				return new Employee(empID, first, last, business, start, finished, wage, SIN, street, city, prov, post, home, cell);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/*
	 * the employed parameter is used to say where you want only currently employed employees, if so, then set that to true and
	 * finishLow and finishHigh to null
	 */
	public ArrayList<Employee> getEmployees(String bus, String firstName, String lastName, Date startLow, Date startHigh, Date finishLow, 
			Date finishHigh, double wage, long SIN, String street, String city, String prov, String postal, String home, String cell, boolean employed) throws InvalidDateBoundException
	{
		String query = "SELECT * FROM `mydb`.`employee` ";
		
		boolean first = true;
		boolean empty = true;
		
		if (firstName != null) {
			first = false;
			query += "WHERE `first name` = '" + firstName + "'";
			
			empty = false;
		}
		
		if (lastName != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `last name` = '" + lastName + "'";
			
			empty = false;
		}
		
		if (startLow != null && startHigh != null) {
			if (startLow.after(startHigh)) {
				throw new InvalidDateBoundException("start date is later than finished date");
			}
			
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Date Hired` >= '" + startLow + "' AND `Date Hired` <= '" + startHigh + "'";
			
			empty = false;
		}
		
		if (finishLow != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Date Fired` >= '" + finishLow + "'";
			
			empty = false;
		}
		
		if (finishHigh != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += "`Date Fired` <= '" + finishHigh + "'";
			
			empty = false;
		}
		
		if (employed) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Date Fired` IS NULL";
			
			empty = false;
		}
		
		if (wage > 0) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Wage` = '" + wage + "'";
			
			empty = false;
		}
		
		if (SIN > 0) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `SIN` = '" + SIN + "'";
			
			empty = false;
		}
		
		if (street != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Street Address` = '" + street + "'";
			
			empty = false;
		}
		
		if (city != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `City` = '" + city + "'";
			
			empty = false;
		}
		
		if (prov != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Province` = '" + prov + "'";
			
			empty = false;
		}
		
		if (postal != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Postal Code` = '" + postal + "'";
			
			empty = false;
		}
		
		if (home != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Home Phone` = '" + home + "'";
			
			empty = false;
		}
		
		if (cell != null) {
			if (first) {
				query += "WHERE";
				first = false;
			}
			
			else {
				query += " AND";
			}
			
			query += " `Cell Phone` = '" + cell + "'";
			
			empty = false;
		}
		
		if (!empty) {
			query += " AND";
		}
		
		else {
			query += "WHERE";
		}
		
		query += " `Businessid` = '" + bus + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			ArrayList<Employee> employees = new ArrayList<Employee>();
			
			while (rs.next()) {
				long id1 = rs.getLong("idEmployee");
				String first1 = rs.getString("first name");
				String last1 = rs.getString("last name");
				String bus1 = rs.getString("Businessid");
				Date start1 = rs.getDate("Date Hired");
				Date finish1 = rs.getDate("Date Fired");
				double wage1 = rs.getDouble("Wage");
				long SIN1 = rs.getLong("SIN");
				String street1 = rs.getString("Street Address");
				String city1 = rs.getString("City");
				String prov1 = rs.getString("Province");
				String postal1 = rs.getString("Postal Code");
				String home1 = rs.getString("Home Phone");
				String cell1 = rs.getString("Cell Phone");
				
				employees.add(new Employee(id1, first1, last1, bus1, start1, finish1, wage1, SIN1, street1, city1, prov1, postal1, home1, cell1));
			}
			
			statement.close();
			return employees;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public boolean removeEmployee(long empID)
	{
		String query = "DELETE FROM `mydb`.employee WHERE `idEmployee` = '" + empID	+"';";
		
		try {
			Statement statement = con.createStatement();
			int removed = statement.executeUpdate(query);
			
			if (removed != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}
	
	public ArrayList<Employee> getEmployeesOnJob(long jobID)
	{
		String query = "SELECT * FROM `mydb`.`Employee` WHERE `idEmployee` IN (SELECT `Employee` FROM `mydb`.`TimeSheet`" +
																				"WHERE `Job` = '" + jobID +"');";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<Employee> employees = new ArrayList<Employee>();
			
			while (rs.next()) {
				long empID = rs.getLong("idEmployee");
				String firstName = rs.getString("first name");
				String lastName = rs.getString("last name");
				String busName = rs.getString("Businessid");
				Date start = rs.getDate("Date Hired");
				Date finished = rs.getDate("Date Fired");
				double wage = rs.getDouble("Wage");
				long SIN = rs.getLong("SIN");
				String streetAd = rs.getString("Street Address");
				String city = rs.getString("City");
				String province = rs.getString("Province");
				String postalCode = rs.getString("Postal Code");
				String homeTel = rs.getString("Home Phone");
				String cell = rs.getString("Cell Phone");
				
				Employee employee = new Employee(empID, firstName, lastName, busName, start, finished, wage, SIN, streetAd, city, province, postalCode, homeTel, cell);
				employees.add(employee);
			}
			
			statement.close();
			return employees;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	//JOB METHODS
	/*
	 * IMPORTANT!!! Must be signed in
	 */
	
	/*
	 * NOTE: client and busName must refer to a valid clientid and a valid business name (i.e. must be in the database) or else
	 * an foreign key constraint exception is printed
	 */
	public Job addNewJob(long client, double estP, double actP, double amtP, Date expSDate, Date actSDate, Date expFDate, Date actFDate,
			String street, String city, String prov, String busName)
	{
		//`idJob`, `Clientid`, `Estimated Price`, `Actual Price`, `Amount Paid`, `Expected Start Date`, `Actual Start Date`, `Street Address`, `City`, `Province`, `Business Name`
		String query = "INSERT INTO `mydb`.`Job` (`Clientid`, `Estimated Price`, `Actual Price`, `Amount Paid`, `Expected Start Date`, `Actual Start Date`, " +
				"`Expected Finish Date`, `Actual Finish Date`, `Street Address`, `City`, `Province`, `Business Name`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		
		try {
			PreparedStatement statement = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			
			statement.setLong(1, client);
			statement.setDouble(2, estP);
			statement.setDouble(3, actP);
			statement.setDouble(4, amtP);
			statement.setDate(5, expSDate);
			statement.setDate(6, actSDate);
			statement.setDate(7, expFDate);
			statement.setDate(8, actFDate);
			statement.setString(9, street);
			statement.setString(10, city);
			statement.setString(11, prov);
			statement.setString(12, busName);
			
			statement.execute();
			
			ResultSet rs = statement.getGeneratedKeys();
			
			if (rs.next()) {
				con.commit();
				Job j = new Job(rs.getLong(1), client, estP, actP, amtP, expSDate, 
								actSDate, expFDate, actFDate, street, city, prov, busName);
				statement.close();
				return j;
			}
			
			con.rollback();
			statement.close();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return null;
	}
	
	public boolean updateJob(long jobID, double estP, double actP, double amtP, Date expSDate, Date actSDate, Date expFDate, Date actFDate,
			String street, String city, String prov)
	{
		String query = "UPDATE `mydb`.`Job` SET";
		boolean isfirst = true;
		boolean isEmpty = true;
		
		if (estP >= 0) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Estimated Price` = '" + estP + "'";
			isEmpty = false;
		}
		
		if (actP >= 0) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Actual Price` = '" + actP + "'";
			isEmpty = false;
		}
		
		if (amtP >= 0)  {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Amount Paid` = '" + amtP + "'";
			isEmpty = false;
		}
		
		if (expSDate != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Expected Start Date` = '" + expSDate + "'";
			isEmpty = false;
		}
		
		if (actSDate != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Actual Start Date` = '" + actSDate + "'";
			isEmpty = false;
		}
		
		if (expFDate != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Expected Finish Date` = '" + expFDate + "'";
			isEmpty = false;
		}
		
		if (actFDate != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Actual Finish Date` = '" + actFDate + "'";
			isEmpty = false;
		}
		
		if (street != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Street Address` = '" + street + "'";
			isEmpty = false;
		}
		
		if (city != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `City` = '" + city + "'";
			isEmpty = false;
		}
		
		if (prov != null) {
			if (!isfirst) {
				query += ",";
			}
			
			else {
				isfirst = false;
			}
			
			query += " `Province` = '" + prov + "'";
			isEmpty = false;
		}
		
		if (isEmpty) {
			return false;
		}
		
		query += " WHERE `idJob` = '" + jobID + "';";
		
		try {
			Statement statement = con.createStatement();
			
			int updated = statement.executeUpdate(query);
				
			if (updated != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		return false;
	}
	
	public Job getJob(long jobID)
	{
		String query = "SELECT * FROM `mydb`.`Job` WHERE `idJob` = '" + jobID + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				long clientID = rs.getLong("ClientID");
				String business = rs.getString("Business name");
				double estP = rs.getDouble("Estimated Price");
				double actP = rs.getDouble("Actual Price");
				double amtP = rs.getDouble("Amount Paid");
				Date expSD = rs.getDate("Expected Start Date");
				Date actSD = rs.getDate("Actual Start Date");
				Date expFD = rs.getDate("Expected Finish Date");
				Date actFD = rs.getDate("Actual Finish Date");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				
				statement.close();
				return new Job(jobID, clientID, estP, actP, amtP, expSD, actSD, expFD, actFD, street, city, prov, business);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public ArrayList<Job> getClientsJobs(long clientID, String business)
	{
		String query = "SELECT * FROM `mydb`.`Job` WHERE `Clientid` = '" + clientID	+ "' AND `Business name` = '" + business + "';";
		
		try {
			Statement statement = con.createStatement();
			
			ResultSet rs = statement.executeQuery(query);
			ArrayList<Job> jobs = new ArrayList<Job>();
			
			while (rs.next()) {
				long id = rs.getLong("idJob");
				double estP = rs.getDouble("Estimated Price");
				double actP = rs.getDouble("Actual Price");
				double amtP = rs.getDouble("Amount Paid");
				Date expSD = rs.getDate("Expected Start Date");
				Date actSD = rs.getDate("Actual Start Date");
				Date expFD = rs.getDate("Expected Finish Date");
				Date actFD = rs.getDate("Actual Finish Date");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				
				jobs.add(new Job(id, clientID, estP, actP, amtP, expSD, actSD, expFD, actFD, street, city, prov, business));
			}
			statement.close();
			return jobs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	//NEEDS TESTING
	public ArrayList<Job> getEmployeesJobs(long employeeID, String business)
	{
		String query = "SELECT * " +
						"FROM `mydb`.`Job` AS `j` " +
						"WHERE `Business name` = '" + business + "' AND EXISTS " +
								"(" +
									"SELECT * " +
									"FROM `mydb`.`TimeSheet` AS `t` " +
									"WHERE `t`.`Employee` = '" + employeeID + "' AND `j`.idJob = `t`.`Job`" +
								");";
				
		try {
			Statement statement = con.createStatement();
			
			ResultSet rs = statement.executeQuery(query);
			ArrayList<Job> jobs = new ArrayList<Job>();
			
			while (rs.next()) {
				long id = rs.getLong("idJob");
				long clientID = rs.getLong("ClientID");
				double estP = rs.getDouble("Estimated Price");
				double actP = rs.getDouble("Actual Price");
				double amtP = rs.getDouble("Amount Paid");
				Date expSD = rs.getDate("Expected Start Date");
				Date actSD = rs.getDate("Actual Start Date");
				Date expFD = rs.getDate("Expected Finish Date");
				Date actFD = rs.getDate("Actual Finish Date");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				
				jobs.add(new Job(id, clientID, estP, actP, amtP, expSD, actSD, expFD, actFD, street, city, prov, business));
			}
			
			statement.close();
			return jobs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	/*
	 * the finished parameter is used to tell the method whether you want to include finished jobs (i.e. jobs where the 
	 * actual finish date field is not null). -1 means you want only finished jobs, 0 means you want only unfinished jobs,
	 * and any other value means you will take all types of jobs. NOTE: if you input 0 the fLow and fHigh must be null or
	 * you will be returned an empty set.
	 */
	//NEEDS TESTING
	public ArrayList<Job> getJobsBetweenDates(String business, Date estSLow, Date estSHigh, Date sLow, Date sHigh,
			Date estFLow, Date estFHigh, Date fLow, Date fHigh, int finished)
	{
		String query = "SELECT * FROM `mydb`.`Job`";
		boolean isfirst = true;
		
		if (estSLow != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			query += " `Expected Start Date` >= '" + estSLow + "'";
		}
		
		if (estSHigh != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Expected Start Date` <= '" + estSHigh + "'";
		}
		
		if (sLow != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Start Date` >= '" + sLow + "'";
		}
		
		if (sHigh != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Start Date` <= '" + sHigh + "'";
		}
		
		if (estFLow != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Expected Finish Date` >= '" + estFLow + "'";
		}
		
		if (estFHigh != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Expected Finish Date` <= '" + estFHigh + "'";
		}
		
		if (fLow != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Finish Date` >= '" + fLow + "'";
		}
		
		if (fHigh != null) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Finish Date` <= '" + fHigh + "'";
		}
		
		if (finished == -1)  {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Finish Date` IS NOT NULL";
		}
		
		else if (finished == 0) {
			if (isfirst) {
				isfirst = false;
				query += " WHERE";
			}
			
			else {
				query += " AND";
			}
			
			query += " `Actual Finish Date` IS NULL";
		}
		
		if (isfirst) {
			isfirst = false;
			query += " WHERE";
		}
		else {
			query += " AND";
		}
		
		query += " `Business name` = '" + business + "';";
		try {
			Statement statement = con.createStatement();
			
			ResultSet rs = statement.executeQuery(query);
			ArrayList<Job> jobs = new ArrayList<Job>();
			
			while (rs.next()) {
				long id = rs.getLong("idJob");
				long clientID = rs.getLong("ClientID");
				double estP = rs.getDouble("Estimated Price");
				double actP = rs.getDouble("Actual Price");
				double amtP = rs.getDouble("Amount Paid");
				Date expSD = rs.getDate("Expected Start Date");
				Date actSD = rs.getDate("Actual Start Date");
				Date expFD = rs.getDate("Expected Finish Date");
				Date actFD = rs.getDate("Actual Finish Date");
				String street = rs.getString("Street Address");
				String city = rs.getString("City");
				String prov = rs.getString("Province");
				
				jobs.add(new Job(id, clientID, estP, actP, amtP, expSD, actSD, expFD, actFD, street, city, prov, business));
			}
			
			statement.close();
			return jobs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return null;
	}
	
	public boolean removeJob(long jobid)
	{
		String update = "DELETE FROM `mydb`.`Job` WHERE `idJob` = '" + jobid + "';";
		
		try {
			Statement statement = con.createStatement();
			int deleted = statement.executeUpdate(update);
			
			if (deleted == 0) {
				statement.close();
				return false;
			}
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	//TIME SHEET METHODS
	/*
	 * IMPORTANT!!! Must be signed in to use
	 */
	
	/*
	 * job, employee, and date arguments cannot be null and must be unique together (i.e. cannot exists a time sheet with all 3 
	 * values the same), and job and employee must refer to valid jobs and employees.
	 */
	public TimeSheet addTimeSheet(long job, long employee, Date date, double hoursWorked, boolean payed)
	{
		String query = "INSERT INTO `mydb`.`TimeSheet` (`Job`, `Employee`, `Date`, `hours work`, `PayedOut`)" +
					"VALUES (?,?,?,?,?)";
		
		try {
			PreparedStatement statement = con.prepareStatement(query);
			
			statement.setLong(1, job);
			statement.setLong(2, employee);
			statement.setDate(3, date);
			statement.setDouble(4, hoursWorked);
			statement.setBoolean(5, payed);
			
			statement.execute();
			
			con.commit();
			statement.close();
			return new TimeSheet(job, employee, date, hoursWorked, payed);
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return null;
	}
	
	public TimeSheet getTimeSheet(long jobID, long empID, Date date)
	{
		String query = "SELECT * FROM `mydb`.`TimeSheet` WHERE `Job` = '" + jobID + "' AND `Employee` = '" + empID + "' AND `Date` = '" + date + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				double hours = rs.getDouble("hours work");
				boolean payed = rs.getBoolean("PayedOut");
				statement.close();
				return new TimeSheet(jobID, empID, date, hours, payed);
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return null;
	}
	
	public ArrayList<TimeSheet> getTimeSheets(long jobID, long empID, Date dateLow, Date dateHigh, String business)
	{
		String query = "SELECT * FROM `mydb`.`TimeSheet`";
		boolean isfirst = true;
		
		if (jobID >= 0) {
			isfirst = false;
			query += " WHERE `Job`= '" + jobID + "'";
		}
		
		if (empID >= 0) {
			if (isfirst) {
				query += " WHERE";
				isfirst = false;
			}
			else{
				query += " AND";
			}
			
			query += " `Employee` = '" + empID + "'";
		}
		
		if (dateLow != null) {
			if (isfirst) {
				query += " WHERE";
				isfirst = false;
			}
			else{
				query += " AND";
			}
			
			query += " `Date` >= '" + dateLow + "'";
		}
		
		if (dateHigh != null) {
			if (isfirst) {
				query += " WHERE";
				isfirst = false;
			}
			else{
				query += " AND";
			}
			
			query += " `Date` <= '" + dateHigh + "'";
		}
		
		if (isfirst) {
			query += " WHERE `Employee` IN (SELECT `idEmployee` FROM `mydb`.`Employee` WHERE `Businessid` = '" + business + "')";
		}
		
		query += ";";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<TimeSheet> sheets = new ArrayList<TimeSheet>();
			while (rs.next()) {
				long job = rs.getLong("Job");
				long employee = rs.getLong("employee");
				Date date = rs.getDate("Date");
				double hours = rs.getDouble("hours work");
				boolean payed = rs.getBoolean("PayedOut");
				
				sheets.add(new TimeSheet(job, employee, date, hours, payed));
			}
			statement.close();
			return sheets;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	/*
	 * Set values to either negative or null if you don't want that value updated.
	 * NOTE: the values of job, employee, and date cannot be update but are used reference a specific TimeSheet
	 */
	public boolean editTimeSheet(long job, long employee, Date date, double hoursWorked, Boolean payed)
	{
		String update = "UPDATE `mydb`.`TimeSheet` SET";
		
		boolean isFirst = true;
		boolean isEmpty	= true;
		
		if (hoursWorked >= 0) {
			isFirst = false;
			isEmpty = false;
			
			update += " `hours work` = '" + hoursWorked + "'";
		}
		
		if (payed != null) {
			if (!isFirst) {
				update += ",";
			}
			
			update += " `PayedOut` = " + payed.booleanValue();
			
			isEmpty = false;
		}
		
		if (isEmpty) {
			return false;
		}
		
		update += " WHERE `Job` = '"+ job +"' AND `Employee` = '"+ employee +"' AND Date = '" + date + "';";
		
		try {
			Statement statement = con.createStatement();
			statement.execute(update);
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	public ArrayList<TimeSheet> getEmployeesTimeSheets(long employeeID, Date low, Date high, Boolean payed)
	{
		String query = "SELECT * FROM `mydb`.`TimeSheet` WHERE `Employee` = '" + employeeID + "'";
		
		if (low != null) {
			query += " AND `Date` >= '" + low + "'";
		}
		
		if (high != null) {
			query += " AND `Date` <= '" + high +"'";
		}
		
		if (payed != null) {
			query += " AND `PayedOut` = " + payed;
		}
		
		query += ";";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<TimeSheet> sheets = new ArrayList<TimeSheet>();
			
			while(rs.next()) {
				long job = rs.getLong("Job");
				Date date = rs.getDate("Date");
				double hours = rs.getDouble("hours work");
				boolean wasPayed = rs.getBoolean("PayedOut");
				
				sheets.add(new TimeSheet(job, employeeID, date, hours, wasPayed));
			}
			
			statement.close();
			return sheets;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public boolean markTimeSheetsPayed(long employee, Date start, Date finished)
	{
		String update = "UPDATE `mydb`.`TimeSheet` SET `PayedOut` = true WHERE `Date` >= '" + start + "' AND `Date` <= '" + finished + "'" +
				" AND `PayedOut` = false;";
		
		try {
			Statement statement = con.createStatement();
			int updated = statement.executeUpdate(update);
						
			if (updated != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean removeTimeSheet(long employee, long job, Date date)
	{
		String update = "DELETE FROM `mydb`.`TimeSheet` WHERE `Job` = '" + job + "' AND `Employee` = '" + employee + "' AND `Date` = '" + date + "';";
		
		try {
			Statement statement = con.createStatement();
			int removed = statement.executeUpdate(update);
			
			if (removed != 0) {
				con.commit();
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	//REVIEWER METHODS
	
	public Reviewer addReviewer(String userName, String password, String email)
	{
		String update1 = "INSERT INTO `mydb`.`Reviewer` (`Reviewerid`, `reviewer email`, `reviewer password`) " +
					" VALUES (?,?,?);";
		
		try {
			PreparedStatement statement1 = con.prepareStatement(update1);
			
			statement1.setString(1, userName);
			statement1.setString(2, email);
			statement1.setString(3, password);
			
			statement1.execute();
			int count = statement1.getUpdateCount();
			
			if (count == 0) {
				throw new InsertionFailureException("failed to insert into reviewer");
			}
			
			con.commit();
			statement1.close();
			return new Reviewer(userName, email, null);
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (InsertionFailureException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return null;
	}
	
	public boolean editReviewer(String userName, String password, String newPassword, String newEmail) throws InvalidReviewerAuthenticationException
	{
		if (!authenticateReviewer(userName, password)) {
			throw new InvalidReviewerAuthenticationException("authentication failed");
		}
		
		String update = "UPDATE `mydb`.`Reviewer` SET";
		boolean isfirst = true;
		boolean empty = true;
		
		if (newPassword != null) {
			isfirst = false;
			empty = false;
			
			update += " `reviewer password` = '" + newPassword + "'";
		}
		
		if (newEmail != null) {
			empty = false;
			
			if (!isfirst) {
				update += ",";
			}
			
			update += " `reviewer email` = '" + newEmail + "'";
		}
		
		if (empty) {
			return false;
		}
		
		update += " WHERE `Reviewerid` = '" + userName + "';";
		
		try {
			Statement statement = con.createStatement();
			int count = statement.executeUpdate(update);
			
			if (count == 0) {
				statement.close();
				return false;
			}
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean subscribeToBusiness(String userName, String password, String businessName) throws InvalidReviewerAuthenticationException 
	{
		String update = "INSERT INTO `mydb`.`Bus Review Connect` (`Reviewer`, `BusName`)" +
				" VALUES (?,?);";
		
		if (!authenticateReviewer(userName, password)) {
			throw new InvalidReviewerAuthenticationException("invalid username password combo");
		}
		
		try {
			PreparedStatement statement2 = con.prepareStatement(update);
				
			statement2.setString(1, userName);
			statement2.setString(2, businessName);
				
			statement2.execute();
				
			con.commit();
			statement2.close();
			return true;
				
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean removeSubscription(String userName, String password, String business) throws InvalidReviewerAuthenticationException
	{
		
		String update = "DELETE FROM `mydb`.`Bus Review Connect` WHERE `Reviewer` = '" + userName + "' AND `BusName` = '" + business + "';";
		
		if (!authenticateReviewer(userName, password)) {
			throw new InvalidReviewerAuthenticationException("invalid username password combo");
		}
		
		try {
			Statement statement = con.createStatement();
			int deleted = statement.executeUpdate(update);
			
			if (deleted == 0) {
				statement.close();
				throw new DeletionFailedException("failed to delete reviewer from business");
			}
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} catch (DeletionFailedException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean removeReviewer(String userName, String password) throws InvalidReviewerAuthenticationException
	{
		String update1 = "DELETE FROM `mydb`.`Bus Review Connect` WHERE `Reviewer` = '" + userName + "';";
		String update2 = "DELETE FROM `mydb`.`Reviewer` WHERE `Reviewerid` = '" + userName + "'";
		
		if (!authenticateReviewer(userName, password)) {
			throw new InvalidReviewerAuthenticationException("invalid username password combo");
		}
		
		try {
			Statement statement1 = con.createStatement();
			Statement statement2 = con.createStatement();
			
			statement1.executeUpdate(update1);
			int deleted = statement2.executeUpdate(update2);
			
			if (deleted == 0) {
				statement1.close();
				statement2.close();
				throw new DeletionFailedException("reviewer was not removed");
			}
			
			con.commit();
			statement1.close();
			statement2.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (DeletionFailedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			con.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean authenticateReviewer(String userName, String password)
	{
		String query = "SELECT * FROM `mydb`.`Reviewer` WHERE `Reviewerid` = '" + userName + "' AND `reviewer password` = '" + password + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				statement.close();
				return true;
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return false;
	}
	
	public String getEmail(String userName)
	{
		String query = "SELECT `reviewer email` FROM `mydb`.`Reviewer` WHERE `Reviewerid` = '" + userName + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				String s = rs.getString("reviewer email");
				statement.close();
				return s;
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public String[] getSubscriberEmails(String business)
	{
		String query = "SELECT `reviewer email` FROM `mydb`.`Reviewer` WHERE `Reviewerid` IN " +
						"(" +
							"SELECT `Reviewerid` FROM `mydb`.`Bus Review Connect` WHERE `BusName` = '" + business + "'" +
						")";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<String> emails = new ArrayList<String>();
			
			if (rs.next()) {
				while (rs.next()) {
					emails.add(rs.getString("reviewer email"));
				}
				statement.close();
				return (String[]) emails.toArray(new String[ emails.size() ]);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	public Reviewer getReviewerInfo(String user, String password) throws InvalidReviewerAuthenticationException
	{
		if (!authenticateReviewer(user,password)) {
			throw new InvalidReviewerAuthenticationException("invalid user name password combo");
		}
		
		String query1 = "SELECT `reviewer email` FROM `mydb`.`Reviewer` WHERE `Reviewerid` = '" + user + "';";
		String query2 = "SELECT `BusName` FROM `mydb`.`Bus Review Connect` WHERE `Reviewer` = '" + user + "';";
		
		try {
			String email = "";
			ArrayList<String> subscriptions = new ArrayList<String>();
			
			Statement statement1 = con.createStatement();
			Statement statement2 = con.createStatement();
			
			ResultSet rs = statement1.executeQuery(query1);
			if (rs.next()) {
				email = rs.getString("reviewer email");
			}
			
			rs = statement2.executeQuery(query2);
			while (rs.next()) {
				subscriptions.add(rs.getString("BusName"));
			}

			statement1.close();
			statement2.close();
			return new Reviewer(user, email, subscriptions.toArray(new String[subscriptions.size()]));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	//REVIEW METHODS
	
	public boolean addReview(String userName, String password, String business, String content, Date date) throws InvalidReviewerAuthenticationException
	{
		if(!authenticateReviewer(userName,password)) {
			throw new InvalidReviewerAuthenticationException("authentication failed");
		}
		
		String update = "INSERT INTO `mydb`.`ReviewEntry` (`ReviewUserid`, `Date Posted`, `content`, `reviewedBusiness`) " +
					"VALUES (?,?,?,?);";
		
		try {
			PreparedStatement statement = con.prepareStatement(update, Statement.RETURN_GENERATED_KEYS);
			
			statement.setString(1, userName);
			statement.setDate(2, date);
			statement.setString(3, content);
			statement.setString(4, business);
			
			int count = statement.executeUpdate();
			
			if (count == 0)
			{
				statement.close();
				return false;
			}
			
			con.commit();
			statement.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	
	public ReviewEntry getReview(long id)
	{
		String query = "SELECT * FROM `mydb`.`ReviewEntry` WHERE `ReviewID` = '" + id + "';";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			if (rs.next()) {
				String userid = rs.getString("Reviewuserid");
				Date date = rs.getDate("Date Posted");
				String content = rs.getString("content");
				String business = rs.getString("reviewedBusiness");
				
				statement.close();
				return new ReviewEntry(id, userid, date, content, business);
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	public ArrayList<ReviewEntry> getReviewsByDate(int number, String business)
	{
		String query = "SELECT * FROM `mydb`.`ReviewEntry` WHERE `reviewedBusiness` = '" + business + "' ORDER BY `Date Posted` DESC;";
		
		try {
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			
			ArrayList<ReviewEntry> reviews = new ArrayList<ReviewEntry>();
			
			for (int index = number; rs.next() && index>0; index--) {
				long id = rs.getLong("ReviewID");
				String userID = rs.getString("ReviewUserid");
				Date date = rs.getDate("Date Posted");
				String content = rs.getString("content");
				
				reviews.add(new ReviewEntry(id, userID, date, content, business));
			}
			statement.close();
			return reviews;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	
	public boolean removeReview(int id) 
	{
		String update = "DELETE FROM `mydb`.`ReviewEntry` WHERE `ReviewID` = '" + id + "';";
		
		try {
			Statement statement = con.createStatement();
			int count = statement.executeUpdate(update);
			
			if (count == 0) {
				statement.close();
				return false;
			}
			
			con.commit();
			
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return false;
	}
	
	public static void main(String args[])
	{
		DatabaseConnector d = new DatabaseConnector();
	}
}
